﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace PookSistener.Product
{
    public partial class frmInventory : Form
    {
        internal OleDbConnection conn = null;

        List<Product> product = new List<Product>();

        string MODE = "";

        public frmInventory()
        {
            InitializeComponent();
        }

        private void frmInventory_Load(object sender, EventArgs e)
        {
            dataGridView1.AutoGenerateColumns = false;

            if (conn == null)
            {
                conn = new OleDbConnection(PookSistener.Properties.Settings.Default.DatabaseConnectionString);
            }

            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }

            conn.Open();
       
            ListAll();

            dataGridView1.DataSource = null;
            dataGridView1.DataSource = dataSet1.Tables["Product"];
            dataGridView1.Invalidate();

            OleDbDataAdapter a1 = new OleDbDataAdapter("SELECT * FROM Category", conn);
            a1.Fill(dataSet1, "Category");

            a1 = new OleDbDataAdapter("SELECT * FROM SubCategory", conn);
            a1.Fill(dataSet1, "SubCategory");

            cboCategory.DataSource = dataSet1.Tables["Category"];
            cboCategory.DisplayMember = "Name";
            cboCategory.ValueMember = "ID";
        }

        private void ListAll()
        {
            if (dataSet1.Tables["Product"] != null)
            {
                dataSet1.Tables["Product"].Clear();
            }

            OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM Product", conn);
            adapter.Fill(dataSet1, "Product");
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (dataGridView1.Rows.Count > 0)
            {
                if (dataGridView1.SelectedRows.Count > 0)
                {
                    if (DialogResult.Yes == MessageBox.Show("คุณต้องการลบข้อมูล ใช่หรือไม่", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
                    {
                        DataGridViewRow row = dataGridView1.SelectedRows[0];
                        try
                        {
                            string product_id = row.Cells["ID"].Value.ToString();

                            OleDbCommand cmd = new OleDbCommand();
                            cmd.CommandText = "DELETE FROM Product WHERE ID=@ID";
                            cmd.CommandType = CommandType.Text;
                            cmd.Connection = conn;
                            cmd.Parameters.Clear();

                            cmd.Parameters.Add("@ID", OleDbType.VarChar).Value = product_id;

                            cmd.ExecuteNonQuery();

                            MessageBox.Show("ลบข้อมูลเรียบร้อยแล้ว");

                            ListAll();

                            dataGridView1.DataSource = null;
                            dataGridView1.DataSource = dataSet1.Tables["Product"];
                            dataGridView1.Invalidate();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Error : " + ex.Message);
                        }
                    }
                }
            }
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            if (dataGridView1.Rows.Count > 0)
            {
                if (dataGridView1.SelectedRows.Count > 0)
                {
                    MODE = "EDIT";
                    DataGridViewRow row = dataGridView1.SelectedRows[0];

                    panel1.Visible = true;
                    panel1.Left = this.Left + 10;
                    panel1.Top = this.Top + 10;
                    panel1.Width = this.Width - 10;
                    panel1.Height = this.Height - 10;


                    txtID.ReadOnly = true;
                    txtID.Text = row.Cells["ID"].Value.ToString();
                    txtName.Text = row.Cells["tName"].Value.ToString();
                    txtUnit.Text = row.Cells["Unit"].Value.ToString();
                    txtBarcode.Text = row.Cells["Barcode"].Value.ToString();
                    cboCategory.SelectedValue = row.Cells["Category"].Value.ToString();

                }
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            MODE = "ADD";
            
            panel1.Visible = true;
            panel1.Left = this.Left + 10;
            panel1.Top = this.Top + 10;
            panel1.Width = this.Width - 10;
            panel1.Height = this.Height - 10;


            ClearData();

        }

        private void cmdClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {

            if (txtFilter.Text.Trim().Length == 0)
            {
                dataGridView1.DataSource = null;
                dataGridView1.DataSource = dataSet1.Tables["Product"];
                dataGridView1.Invalidate();
            }
            else
            {
                List<Product> filter = new List<Product>();

                for (int i = 0; i < dataSet1.Tables["Product"].Rows.Count; i++)
                {
                    DataRow row = dataSet1.Tables["Product"].Rows[i];

                    if (row["Name"].ToString().IndexOf(txtFilter.Text) != -1)
                    {
                        Product item = new Product();
                        item.ID = Convert.ToInt32(row["ID"].ToString());
                        item.Name = row["Name"].ToString();
                        item.Category = Convert.ToInt32(row["Category"].ToString());
                        item.Subcategory = Convert.ToInt32(row["SubCategory"].ToString());
                        item.Unit = Convert.ToInt32(row["Unit"].ToString());
                        item.Barcode = row["Barcode"].ToString();

                        filter.Add(item);
                        continue;
                    }

                    if (row["Barcode"].ToString().IndexOf(txtFilter.Text) != -1)
                    {
                        Product item = new Product();
                        item.ID = Convert.ToInt32(row["ID"].ToString());
                        item.Name = row["Name"].ToString();
                        item.Category = Convert.ToInt32(row["Category"].ToString());
                        item.Subcategory = Convert.ToInt32(row["SubCategory"].ToString());
                        item.Unit = Convert.ToInt32(row["Unit"].ToString());
                        item.Barcode = row["Barcode"].ToString();

                        filter.Add(item);
                        continue;
                    }
                }

                dataGridView1.DataSource = filter;
                dataGridView1.Invalidate();
            }
        }

        private void txtFilter_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                btnSearch_Click(null, null);
            }
        }

        private void txtFilter_TextChanged(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            panel1.Visible = false;
            MODE = "";

            ListAll();

            dataGridView1.DataSource = null;
            dataGridView1.DataSource = dataSet1.Tables["Product"];
            dataGridView1.Invalidate();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (MODE.Equals("ADD"))
            {
               
                string sql = "INSERT INTO Product(ID, Name, Barcode, SubCategory, Category, Unit) VALUES (@ID, @Name, @Barcode, @SubCategory, @Category, @Unit)";

                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.Connection = conn;
                cmd.Parameters.Clear();

                cmd.Parameters.Add("@ID", OleDbType.VarChar).Value = txtID.Text;
                cmd.Parameters.Add("@Name", OleDbType.VarChar).Value = txtName.Text;
                cmd.Parameters.Add("@Barcode", OleDbType.VarChar).Value = txtBarcode.Text;
                cmd.Parameters.Add("@SubCategory", OleDbType.Integer).Value = 0;
                cmd.Parameters.Add("@Category", OleDbType.Integer).Value = Convert.ToInt32(cboCategory.SelectedValue);
                cmd.Parameters.Add("@Unit", OleDbType.Double).Value = txtUnit.Text;

                cmd.ExecuteNonQuery();

                MessageBox.Show("เพิ่มข้อมูลเรียบร้อยแล้ว");
                ClearData();

                ListAll();

                dataGridView1.DataSource = null;
                dataGridView1.DataSource = dataSet1.Tables["Product"];
                dataGridView1.Invalidate();

                panel1.Visible = false;
            }
            else if (MODE.Equals("EDIT"))
            {
                string sql = "UPDATE Product SET Name=@Name, Barcode=@Barcode, SubCategory=@SubCategory, Category=@Category, Unit=@Unit WHERE ID=@ID";

                try
                {
                    string product_id = txtID.Text;

                    OleDbCommand cmd = new OleDbCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    cmd.Connection = conn;
                    cmd.Parameters.Clear();

                    cmd.Parameters.Add("@Name", OleDbType.VarChar).Value = txtName.Text;
                    cmd.Parameters.Add("@Barcode", OleDbType.VarChar).Value = txtBarcode.Text;
                    cmd.Parameters.Add("@SubCategory", OleDbType.Integer).Value = 0;
                    cmd.Parameters.Add("@Category", OleDbType.Integer).Value = Convert.ToInt32(cboCategory.SelectedValue);
                    cmd.Parameters.Add("@Unit", OleDbType.Double).Value = txtUnit.Text;
                    cmd.Parameters.Add("@ID", OleDbType.VarChar).Value = product_id;

                    cmd.ExecuteNonQuery();

                    MessageBox.Show("แก้ไขข้อมูลเรียบร้อยแล้ว");

                    ListAll();

                    dataGridView1.DataSource = null;
                    dataGridView1.DataSource = dataSet1.Tables["Product"];
                    dataGridView1.Invalidate();

                    panel1.Visible = false;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("เกิดข้อผิดพลาด [" + ex.Message + "]");
                }
            }
        }

        private void ClearData()
        {
            txtID.ReadOnly = false;
            txtID.Text = "";
            txtName.Text = "";
            txtUnit.Text = "0";
            txtBarcode.Text = "";
            //cboSubCategory.SelectedValue = "";
            //cboCategory.SelectedValue = "";
        }

        private void dataGridView1_Resize(object sender, EventArgs e)
        {

        }

        private void frmInventory_Resize(object sender, EventArgs e)
        {
            int scrollBarWidth = SystemInformation.VerticalScrollBarWidth + 5;
            int rowHeaderWidth = dataGridView1.RowHeadersWidth + 3;

            int width = dataGridView1.Width - scrollBarWidth;

            int remainingWidth = 250;

            dataGridView1.Columns["tName"].Width = (width - remainingWidth);
        }
    }
}
